

/*********************************************************************************/
/*                                                                               */ 
/* Create panel data that describes individuals in our sample                    */
/*                                                                               */ 
/*********************************************************************************/



libname raw 'G:\Data\Rawdata\707677';

libname common 'G:\Data\Workdata\707677\Common\01Data\01SAS';

libname fmt '\\srvfsenas3\formater\SAS formater i Danmarks Statistik\FORMATKATALOG' access=readonly;
options fmtsearch=(fmt.times_personstatistik fmt.times_bbr
fmt.times_erhvervsstatistik fmt.brancher fmt.uddannelser
fmt.geokoder fmt.disco fmt.statistikbank fmt.disced fmt.sundhed);




/*******************/
/* Load our sample */
/*******************/


data list;

set common.list;

keep pnr granted pyear pmonth male;

run;



/*************************/
/* Create panel data set */
/************************/



data years;

set common.list;

keep pyear;

run;



proc sort data=years nodupkey;

by pyear;

run;



data years;

set years;

rename pyear=aar;

run;




data year0;

set years;

aar=aar-4;

run;



data year1;

set years;

aar=aar+16;

run;



data years;

set years year0 year1;

run;




proc sort data=years nodupkey;

by aar;

run;



proc datasets library=work nolist;

delete year0 year1;

quit;




proc sql;

create table list as select *

from list cross join years;

quit;



proc sort data=list;

by pnr aar;

run;



data list;

retain pnr aar;

set list;

run;



proc datasets library=work nolist;

delete years; 

quit;






/*********************/
/* Data from idapall */
/*********************/



data idapall;

set raw.idapall;

keep pnr aar alder aegte_nr faelle_nr slon timelon pstill socstil_kode hfpria kom hfaudd; 

run;





proc datasets library=work nolist;

modify idapall; 

attrib _all_ label='';

quit;




data idapall;

set idapall;

newaar=input(aar,4.0);

run;



data idapall;

set idapall;

drop aar;

rename newaar=aar;

run;




proc sql;

create table list as select *

from list as a left join

idapall as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete idapall;

quit;





/*****************/
/* Data from bef */
/*****************/


data bef;

set raw.bef;

keep pnr aar kom ANTPERSF ANTBOERNF IE_type civst;

run;



proc datasets library=work nolist;

modify bef;

attrib _all_ label='';

quit;



data bef;

set bef;

newaar=input(aar,4.0);

rename kom=kom_new;

run;



data bef;

set bef;

drop aar;

rename newaar=aar;

run;



proc sql;

create table list as select *

from list as a left join

bef as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;



data list;

set list;

if kom='' then kom=kom_new;

run;



data list;

set list;

drop kom_new;

run;



proc datasets library=work nolist;

delete bef;

quit;





/******************/
/* Data from idap */
/******************/


data idap;

set raw.idap;

keep pnr aar JOB_P_STILL_KODE JOB_P_SOCIO_KODE;

run;



proc datasets library=work nolist;

modify idap;

attrib _all_ label='';

quit;



data idap;

set idap;

newaar=input(aar,4.0);

run;



data idap;

set idap;

drop aar;

rename newaar=aar;

run;



proc sql;

create table list as select *

from list as a left join

idap as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete idap;

quit;





/******************/
/* Data from idan */
/******************/



data idan;

set raw.idan;

keep pnr aar job_type JOB_TIME_LOEN_SMAL;

run;


data idan;

set idan;

if JOB_TYPE="H";

run;


proc datasets library=work nolist;

modify idan;

attrib _all_ label='';

quit;



data idan;

set idan;

newaar=input(aar,4.0);

run;



data idan;

set idan;

drop aar;

rename newaar=aar;

run;



proc sql;

create table list as select *

from list as a left join

idan as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete idan;

quit;





/******************/
/* Data from fain */
/******************/



data fain; * Only runs to 2007;

set raw.fain;

keep pnr aar CIVST;

rename civst=civst_01;

run;



proc datasets library=work nolist;

modify fain;

attrib _all_ label='';

quit;



data fain;

set fain;

newaar=input(aar,4.0);

run;



data fain;

set fain;

drop aar;

rename newaar=aar;

run;



proc sql;

create table list as select *

from list as a left join

fain as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete fain;

quit;





/*****************/
/* Data from ind */
/*****************/



data ind;

set raw.ind;

keep pnr aar ANDAKAS ARBLHUMV 

KONTANTHJ_13 KONT_GL QAKTIVF TILBTOT

alder dispon_ny KOEJD LOENMV_13 QAKTIVF_NY05 QPASSIV QPASSIVN PANTGAELD BANKGAELD OBLGAELD

perindkialt NETOVSKUD NETOVSKUD_GL NETOVSKUD_13 ERHVERVSINDK_13;

run;




proc datasets library=work nolist;

modify ind;

attrib _all_ label='';

quit;




data ind;

set ind;

newaar=input(aar,4.0);

run;



data ind;

set ind;

drop aar;

rename newaar=aar;

run;





proc sql;

create table list as select *

from list as a left join

ind as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;



data ind_sp;

set ind;

if aar<1983;

keep pnr aar QAKTIVF KOEJD QAKTIVF_NY05 QPASSIV QPASSIVN PANTGAELD BANKGAELD OBLGAELD;

run;



proc datasets library=work nolist;

delete ind;

quit;



/******************/
/* Data from Indk */
/******************/



data indk;

set raw.indk;

keep pnr aar PRIGALD GALDIO;

run;




proc datasets library=work nolist;

modify indk;

attrib _all_ label='';

quit;




data indk;

set indk;

newaar=input(aar,4.0);

run;



data indk;

set indk;

drop aar;

rename newaar=aar;

run;





proc sql;

create table list as select *

from list as a left join

indk as b on

a.pnr=b.pnr and 

a.aar=b.aar;

quit;




proc datasets library=work nolist;

delete indk;

quit;







/**********************/
/* Organize variables */
/**********************/


data panel;

set list;

run;






data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom ANTBOERNF ANTPERSF

SLON TIMELON DISPON_NY LOENMV_13 perindkialt

QAKTIVF QAKTIVF_NY05  QPASSIV QPASSIVN 

KOEJD 

job_time_loen_smal

NETOVSKUD NETOVSKUD_GL NETOVSKUD_13 ERHVERVSINDK_13 

KONTANTHJ_13 KONT_GL  

ARBLHUMV ANDAKAS

TILBTOT

PSTILL SOCSTIL_KODE     

JOB_P_STILL_KODE JOB_P_SOCIO_KODE;

set panel;

run;




* Use put function with formats ;

data panel; * Set in SAS-program where we extract instead 03panel;
set panel;
edu_type=put(hfaudd, AUDD_LEVEL_L1L4_KT.);
edu_type_extended=put(hfaudd, AUDD_HOVED_L1L5_KT.);
run;



/************************/
/* Create new variables */
/************************/



/* Assets, Debt, and Wealth */


data panel;

set panel;

length AKTIV PASSIV FORMUE 8.0;


if aar<1997 then AKTIV=QAKTIVF;

if aar>1996 then AKTIV=QAKTIVF_NY05;


if aar<1997 then PASSIV=QPASSIV;

if aar>1996 then PASSIV=QPASSIVN;


FORMUE=AKTIV-PASSIV;

run;




/* secured debt */


data panel;

set panel;

mortgage=.;

if aar<1995 then mortgage=PRIGALD;

if aar>1994 then mortgage=OBLGAELD;

run;






data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom ANTBOERNF ANTPERSF

SLON TIMELON DISPON_NY LOENMV_13 perindkialt

AKTIV PASSIV FORMUE;

set panel;

run;




/* Ownership of residential house */


data panel;

set panel;

length house 8.0;

IF KOEJD=0 then house=0;

if KOEJD>0 then house=1;

run;



data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom ANTBOERNF ANTPERSF

SLON TIMELON DISPON_NY LOENMV_13 perindkialt

AKTIV PASSIV FORMUE

KOEJD house;

set panel;

run;




/* Years of schooling. */


data panel;

set panel;

if aar<=2012 then do;

schooling=put(HFPRIA,3.0)/12;

end;

run;



/* Employment */



data panel;

set panel;


if aar<=2012 then do;


employed_pstill=0; 

unemployed_pstill=0; 

out_of_lf_pstill=0;


pstill_num=INPUT(PSTILL,2.0);


if pstill_num=. then employed_pstill=.; 

if pstill_num=. then unemployed_pstill=.;

if pstill_num=. then out_of_lf_pstill=.;


if (pstill_num<38 and pstill_num~=.) or pstill_num in (71,72,73,74,75,76,77) then employed_pstill=1;

if pstill_num=40 then unemployed_pstill=1;

if (pstill_num>40 and pstill_num<58) or (pstill_num in (90,91,92,93,94,95,96,97,98)) then out_of_lf_pstill=1;

end;


if aar>2012 then do;


employed_pstill=0; 

unemployed_pstill=0; 

out_of_lf_pstill=0;

sociokode_num=INPUT(JOB_P_SOCIO_KODE,3.0);


if sociokode_num=. then employed_pstill=.; 

if sociokode_num=. then unemployed_pstill=.;

if sociokode_num=. then out_of_lf_pstill=.;



if sociokode_num=110 or sociokode_num=120 or sociokode_num in(131,132,133,134,135,136) then employed_pstill=1;

if sociokode_num=200 then unemployed_pstill=1;

if sociokode_num>=311 & sociokode_num<=517 then out_of_lf_pstill=1;
end;



run;




data panel;

set panel;

if loenmv_13=0 then employed_loenmv=0;

if loenmv_13>0 then employed_loenmv=1;

run;




data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom ANTBOERNF ANTPERSF

SLON TIMELON DISPON_NY LOENMV_13 perindkialt

AKTIV PASSIV FORMUE

KOEJD house pstill employed_pstill unemployed_pstill out_of_lf_pstill employed_loenmv;

set panel;

drop pstill_num;

run;




/* Disability insurance */


data panel;

set panel;

length disability 4.0;

if tilbtot=0 then disability=0;

if tilbtot>0 then disability=1;

run;



data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom ANTBOERNF ANTPERSF

SLON TIMELON DISPON_NY LOENMV_13 perindkialt

AKTIV PASSIV FORMUE

KOEJD house 

pstill employed_pstill unemployed_pstill out_of_lf_pstill employed_loenmv

tilbtot disability;

set panel;

run;




/* Social assistance */



data panel;

set panel;

kontant=kont_gl;

if aar>1993 then kontant=kontanthj_13;

run;



data panel;

set panel;

drop kont_gl kontanthj_13;

run;



data panel;

set panel;

if kontant=0 then social=0;

if kontant>0 then social=1;

run;



data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom ANTBOERNF ANTPERSF

SLON TIMELON DISPON_NY LOENMV_13 perindkialt

AKTIV PASSIV FORMUE

KOEJD house 

pstill employed_pstill unemployed_pstill out_of_lf_pstill employed_loenmv

tilbtot disability

kontant social;

set panel;

run;




/* Unemployment benefits */



data panel;

set panel;

unempl_benefit=.;

if arblhumv=0 then unempl_benefit=0;

if arblhumv>0 then unempl_benefit=1;

if andakas>0 then unempl_benefit=1;

run;





data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom ANTBOERNF ANTPERSF

SLON TIMELON DISPON_NY LOENMV_13 perindkialt

AKTIV PASSIV FORMUE

KOEJD house 

pstill employed_pstill unemployed_pstill out_of_lf_pstill employed_loenmv

tilbtot disability

kontant social

unempl_benefit;

set panel;

run;




/******************************************************************/
/* Convert nominal variables to real using 2020 price level 104.5 */
/******************************************************************/



data panel;

set panel;

cpi=.;

if aar='1980' then cpi=34.8;

if aar='1981' then cpi=38.9;

if aar='1982' then cpi=42.8;

if aar='1983' then cpi=45.7;

if aar='1984' then cpi=48.6;

if aar='1985' then cpi=50.9;

if aar='1986' then cpi=52.8;

if aar='1987' then cpi=54.9;

if aar='1988' then cpi=57.4;

if aar='1989' then cpi=60.1;

if aar='1990' then cpi=61.7;

if aar='1991' then cpi=63.2;

if aar='1992' then cpi=64.5;

if aar='1993' then cpi=65.3;

if aar='1994' then cpi=66.6;

if aar='1995' then cpi=68.0;

if aar='1996' then cpi=69.5;

if aar='1997' then cpi=71.0;

if aar='1998' then cpi=72.3;

if aar='1999' then cpi=74.1;

if aar='2000' then cpi=76.2;

if aar='2001' then cpi=78.0;

if aar='2002' then cpi=79.9;

if aar='2003' then cpi=81.6;

if aar='2004' then cpi=82.5;

if aar='2005' then cpi=84.0;

if aar='2006' then cpi=85.6;

if aar='2007' then cpi=87.1;

if aar='2008' then cpi=90.1;

if aar='2009' then cpi=91.2;

if aar='2010' then cpi=93.3;

if aar='2011' then cpi=95.9;

if aar='2012' then cpi=98.2;

if aar='2013' then cpi=99.0;

if aar='2014' then cpi=99.6;

if aar='2015' then cpi=100.0;

if aar='2016' then cpi=100.3;

if aar='2017' then cpi=101.4;

if aar='2018' then cpi=102.2;

if aar='2019' then cpi=103.0;

run; 




data panel;

set panel;

Erhvervsindk_real= Erhvervsindk_13*(104.5/cpi); 

SLON_real=SLON*(104.5/cpi); 

TIMELON_real=TIMELON*(104.5/cpi); 

job_time_loen_smal_real=job_time_loen_smal*(104.5/cpi);

DISPON_NY_real=DISPON_NY*(104.5/cpi); 

LOENMV_real=LOENMV_13*(104.5/cpi); 

netovskud_real=netovskud_13*(104.5/cpi); 

perindkialt_real=perindkialt*(104.5/cpi);

AKTIV_real=AKTIV*(104.5/cpi); 

PASSIV_real=PASSIV*(104.5/cpi); 

FORMUE_real=FORMUE*(104.5/cpi);

KOEJD_real=KOEJD*(104.5/cpi); 

tilbtot_real=tilbtot*(104.5/cpi); 

kontant_real=kontant*(104.5/cpi); 

PANTGAELD_real=PANTGAELD*(104.5/cpi); 

BANKGAELD_real=BANKGAELD*(104.5/cpi); 

OBLGAELD_real=OBLGAELD*(104.5/cpi);

PRIGALD_real=PRIGALD*(104.5/cpi); 

GALDIO_real=GALDIO*(104.5/cpi);

mortgage_real=mortgage*(104.5/cpi);

run;




data panel;

retain pnr granted pyear pmonth aar male alder aegte_nr faelle_nr CIVST kom erhvervsindk_real ANTBOERNF ANTPERSF

SLON SLON_real TIMELON TIMELON_real DISPON_NY DISPON_NY_real LOENMV_13 LOENMV_real perindkialt perindkialt_real

AKTIV AKTIV_real PASSIV PASSIV_real FORMUE FORMUE_real

KOEJD KOEJD_real house 

pstill employed_pstill unemployed_pstill out_of_lf_pstill employed_loenmv

tilbtot tilbtot_real disability

kontant kontant_real social 

unempl_benefit;

set panel;

run;



data panel;

set panel;

drop cpi;

run;




/*****************************/
/* Clean data on hourly wage */
/*****************************/



data panel;

set panel;

if timelon=0 then timelon=.;

if timelon_real=0 then timelon_real=.;

if job_time_loen_smal=0 then job_time_loen_smal=.;

if job_time_loen_smal_real=0 then job_time_loen_smal_real=.;




if aar>2010 then do;

timelon_real=job_time_loen_smal_real;

end;


run;





/**********************************************/
/* Construct additional demographic variables */
/**********************************************/




/* Marital status */


data panel;

set panel;

if aar>2006 then do;

legmarried=.;

if civst='G' then legmarried=1;

if civst~='G' and civst~='' then legmarried=0;

end;

if aar<=2006 then do;

legmarried=.;

if civst_01='G' then legmarried=1;

if civst_01~='G' and civst_01~='' then legmarried=0;

end;

run;




/* Age */


data panel;

set panel;

age=input(alder,3.0);

byear=aar-age;

run;



proc sort data=panel;

by pnr aar;

run;



proc summary data=panel;

by pnr;

output out=stats

max(byear)=mbyear;

run;



data stats;

set stats;

drop _type_ _freq_;

run;



proc sql;

create table panel as select *

from panel as a left join

stats as b on

a.pnr=b.pnr;

quit;



data panel;

set panel;

drop byear;

rename mbyear=byear;

run;



data panel;

set panel;

drop age;

run;



data panel;

set panel;

age=aar-byear;

run;




data panel;

retain pnr granted pyear pmonth aar male age alder byear;

set panel;

run;




data panel;

set panel;

drop alder;

run;




/* Time relative to decision */


data panel;

set panel;

time=aar-pyear;

run;




data panel;

retain pnr aar granted time pyear pmonth male age byear;

set panel;

run;





/* Age in year 0 (year when investigation is opened) */



data list;

set common.list;

keep pnr alder;

run;


data list;

set list;

age0=input(alder,3.0)+1;

run; 



data list;

set list;

drop alder;

run;



proc sql;

create table panel as select *

from panel as a left join

list as b on

a.pnr=b.pnr;

quit;



proc sort data=panel;

by pnr aar;

run;



data panel;

retain pnr aar granted time pyear pmonth age0;

set panel;

run;





/*******************************************************************************************************************/
/* Adjust assets, debt, and wealth in 1980-1982 when all variables for married couples are registered with husband */
/*******************************************************************************************************************/


data ind_sp;

set ind_sp;

keep pnr aar QAKTIVF QPASSIV;

run;




data ind_sp;

set ind_sp;

rename pnr=aegte_nr;

rename QAKTIVF=sp_QAKTIVF; 

rename QPASSIV=sp_QPASSIV;

run;




proc sort data=panel nodupkey;

by pnr aar;

run;



proc sort data=ind_sp nodupkey;

by aegte_nr aar;

run;



proc sql;

create table panel as select *

from panel as a left join

ind_sp as b on

a.aegte_nr=b.aegte_nr and

a.aar=b.aar;

quit;





proc sort data=panel nodupkey;

by pnr aar;

run;




data panel;

set panel;

cpi=.;

if aar='1980' then cpi=34.8;

if aar='1981' then cpi=38.9;

if aar='1982' then cpi=42.8;

run;




data panel;

set panel;

sp_QAKTIVF_real=sp_QAKTIVF*(104.5/cpi); 

sp_QPASSIV_real=sp_QPASSIV*(104.5/cpi); 

run;


 
data panel;

set panel;

sp_formue=sp_QAKTIVF-sp_QPASSIV;

sp_formue_real=sp_QAKTIVF_real-sp_QPASSIV_real;

run;




data panel;

set panel;


new_aktiv=aktiv;

new_passiv=passiv;

new_formue=formue;


new_aktiv_real=aktiv_real;

new_passiv_real=passiv_real;

new_formue_real=formue_real;


run;



data panel;

set panel;


if aar<1983 and legmarried=1 then new_aktiv=0.5*(aktiv+sp_QAKTIVF);

if aar<1983 and legmarried=1 then new_passiv=0.5*(passiv+sp_QPASSIV);

if aar<1983 and legmarried=1 then new_formue=0.5*(formue+sp_formue);



if aar<1983 and legmarried=1 then new_aktiv_real=0.5*(aktiv_real+sp_QAKTIVF_real);

if aar<1983 and legmarried=1 then new_passiv_real=0.5*(passiv_real+sp_QPASSIV_real);

if aar<1983 and legmarried=1 then new_formue_real=0.5*(formue_real+sp_formue_real);


run;




data panel;

set panel;

drop aktiv sp_QAKTIVF passiv sp_QPASSIV formue sp_formue aktiv_real sp_QAKTIVF_real passiv_real sp_QPASSIV_real formue_real sp_formue_real;

run;



data panel;

set panel;

rename new_aktiv=aktiv;

rename new_passiv=passiv;

rename new_formue=formue;

rename new_aktiv_real=aktiv_real;

rename new_passiv_real=passiv_real;

rename new_formue_real=formue_real;

run;




/******************************************/
/* Remove variables that we are not using */
/******************************************/


data panel;

set panel;

drop SOCSTIL_KODE cpi QAKTIVF QAKTIVF_NY05 QPASSIV QPASSIVN;

run;




/***************************/
/* Save data on hard drive */
/***************************/



proc sort data=panel nodupkey;

by pnr aar;

run;




data common.panel;

set panel;

run;



proc datasets library=work nolist kill;

quit;







